# -*- coding: utf-8 -*-
"""
author:码同学 极光
date:2025/3/15
desc: 
sample: 
"""
import pymysql
import random
import string
from datetime import datetime, timedelta
from faker import Faker
from pypinyin import pinyin, Style

# 配置数据库连接参数
config = {
    'host': '192.168.79.128',
    'user': 'root',
    'password': '123456',
    'database': 'exam',
    'charset': 'utf8mb4',
    'port': 3307,
    'cursorclass': pymysql.cursors.DictCursor
}

# 初始化 Faker 并设置为中文
fake = Faker('zh_CN')

# 生成随机电话号码
def random_phone():
    return fake.phone_number()

# 生成随机日期
def random_date(start, end):
    delta = end - start
    int_delta = (delta.days * 24 * 60 * 60) + delta.seconds
    random_second = random.randrange(int_delta)
    return start + timedelta(seconds=random_second)

# 插入数据到 student 表
def insert_students(num_records=100):
    connection = pymysql.connect(**config)
    try:
        with connection.cursor() as cursor:
            # 清空 student 表
            cursor.execute("DELETE FROM student")

            sql = "SELECT * FROM `subject`"
            cursor.execute(sql)
            results = cursor.fetchall()
            subject_list=[item.get('id') for item in results]
            print(subject_list)

            # 创建插入数据的 SQL 语句
            sql = """
            INSERT INTO student (subjectId, name, password, trueName, tel, createTime)
            VALUES (%s, %s, %s, %s, %s, %s)
            """
            fake2 = Faker()
            # 生成并插入数据
            for _ in range(num_records):
                subjectId = random.choice(subject_list)  # 假设 subjectId 在 1 到 100 之间 学科id
                #name = fake2.name()  # 英文
                password = random_string(8)
                trueName = fake.name()  # 使用 Faker 生成中文真实名字
                # 将 trueName 转换为拼音
                trueName_pinyin = ''.join([item[0] for item in pinyin(trueName, style=Style.NORMAL)])
                trueName_pinyin=f'{trueName_pinyin}{random.randint(1,100)}'
                tel = random_phone()  # 使用 Faker 生成中文电话号码
                createTime = random_date(datetime(2020, 1, 1), datetime(2023, 12, 31))

                cursor.execute(sql, (subjectId, trueName_pinyin, password, trueName, tel, createTime))

        # 提交事务
        connection.commit()
    finally:
        # 关闭连接
        connection.close()

# 生成随机字符串
def random_string(length=8):
    letters = string.ascii_lowercase
    return ''.join(random.choice(letters) for i in range(length))

if __name__ == '__main__':
    insert_students(10000)
